﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    public class CustomerLibrary
    {
        public long ID = -1;
        public string NAME = "";
        public string CODE = "";
        public string PTNER = "";
        public string COUNTRYCODE = "";
        public string BLNo = "";
        public string VNWRHSE = ""; // Lay MST lam code
        public string POLCODE = "";
        public string POLNAME = "";
        public string PODCODE = "";
        public string PODNAME = "";
        public string DLVRY = "";
        public string CURRENCY = "";
        public float REVENUE = 0;
        public string GOODSCODE = ""; //COMMD CODE - TEN HANG
        public string GOODSNAME = ""; //TEN HANG
        public float VOLUME = 0;
        public string UNIT = "";
        public string MADEIN = "";
        public string ROUTEBY = "";
        public string TYPE = "";
        public int ProvinceID = 0;
        public DateTime CREATEDATE = DateTime.MinValue;
        public DateTime LASTUPDATE = DateTime.Now;

        public CustomerLibrary()
        {
        }

        public CustomerLibrary(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_CustomerLibrary";

            if (ID <= 0) // new object, we insert new record to database
            {
                strSQL = "Insert into " + strTableName + " (" +
                        "`NAME`" +
                        ",`CODE`" +
                        ",`PTNER`" +
                        ",`COUNTRYCODE`" +
                        ",`BLNo`" +
                        ",`VNWRHSE`" +
                        ",`POLCODE`" +
                        ",`POLNAME`" +
                        ",`PODCODE`" +
                        ",`PODNAME`" +
                        ",`DLVRY`" +
                        ",`CURRENCY`" +
                        ",`REVENUE`" +
                        ",`GOODSCODE`" +
                        ",`GOODSNAME`" +
                        ",`VOLUME`" +
                        ",`UNIT`" +
                        ",`MADEIN`" +
                        ",`ROUTEBY`" +
                        ",`TYPE`" +
                        ",`CREATEDATE`" +
                        ",`LASTUPDATE`" +
                        ",`ProvinceID`" +
                        ") " +
                         " values(" +
                         "'" + DBModule.RefineString(NAME) + "'" +
                         ",'" + DBModule.RefineString(CODE) + "'" +
                         ",'" + DBModule.RefineString(PTNER) + "'" +
                         ",'" + DBModule.RefineString(COUNTRYCODE) + "'" +
                        ",'" + DBModule.RefineString(BLNo) + "'" +
                        ",'" + DBModule.RefineString(VNWRHSE) + "'" +
                        "," + "'" + DBModule.RefineString(POLCODE) + "'" +
                        "," + "'" + DBModule.RefineString(POLNAME) + "'" +
                        "," + "'" + DBModule.RefineString(PODCODE) + "'" +
                        "," + "'" + DBModule.RefineString(PODNAME) + "'" +
                        "," + "'" + DBModule.RefineString(DLVRY) + "'" +
                        "," + "'" + DBModule.RefineString(CURRENCY) + "'" +
                        "," + REVENUE.ToString() +
                        "," + "'" + DBModule.RefineString(GOODSCODE) + "'" +
                        "," + "'" + DBModule.RefineString(GOODSNAME) + "'" +
                        "," + VOLUME.ToString() +
                        ",'" + DBModule.RefineString(UNIT) + "'" +
                        ",'" + DBModule.RefineString(MADEIN) + "'" +
                        ",'" + DBModule.RefineString(ROUTEBY) + "'" +
                        ",'" + DBModule.RefineString(TYPE) + "'" +
                        "," + DBModule.RefineDatetime(CREATEDATE) +
                        "," + DBModule.RefineDatetime(LASTUPDATE) +
                        "," + ProvinceID +
                        ")";
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("CustomerLibrary", "add", cn, trans);
            }
            else // edit object
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                            "NAME='" + DBModule.RefineString(NAME) + "'" +
                            ",CODE='" + DBModule.RefineString(CODE) + "'" +
                            ",PTNER='" + DBModule.RefineString(PTNER) + "'" +
                            ",COUNTRYCODE='" + DBModule.RefineString(COUNTRYCODE) + "'" +
                            ",BLNo='" + DBModule.RefineString(BLNo) + "'" +
                            ",VNWRHSE='" + DBModule.RefineString(VNWRHSE) + "'" +
                            ",POLCODE='" + DBModule.RefineString(POLCODE) + "'" +
                            ",POLNAME='" + DBModule.RefineString(POLNAME) + "'" +
                            ",PODCODE='" + DBModule.RefineString(PODCODE) + "'" +
                            ",PODNAME='" + DBModule.RefineString(PODNAME) + "'" +
                            ",DLVRY='" + DBModule.RefineString(DLVRY) + "'" +
                            ",CURRENCY='" + DBModule.RefineString(CURRENCY) + "'" +
                            ",REVENUE = " + REVENUE.ToString() +
                            ",GOODSCODE='" + DBModule.RefineString(GOODSCODE) + "'" +
                            ",GOODSNAME='" + DBModule.RefineString(GOODSNAME) + "'" +
                            ",VOLUME = " + VOLUME.ToString() +
                            ",UNIT = '" + DBModule.RefineString(UNIT) + "'" +
                            ",MADEIN = '" + DBModule.RefineString(MADEIN) + "'" +
                            ",ROUTEBY = '" + DBModule.RefineString(ROUTEBY) + "'" +
                            ",TYPE = '" + DBModule.RefineString(TYPE) + "'" +
                            ",CREATEDATE = " + DBModule.RefineDatetime(CREATEDATE) +
                            ",LASTUPDATE = " + DBModule.RefineDatetime(LASTUPDATE) +
                            ",ProvinceID = " + ProvinceID.ToString() +
                    " where ID=" + ID.ToString();

                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("CustomerLibrary", "edit", cn, trans);
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "DELETE FROM tbl_CustomerLibrary WHERE ID = " + this.ID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("CustomerLibrary", "delete", cn, trans);
        }

        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_CustomerLibrary WHERE ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("NAME")) NAME = dr["NAME"].ToString();
                if (!dr.IsNull("CODE")) CODE = dr["CODE"].ToString();
                if (!dr.IsNull("PTNER")) PTNER = dr["PTNER"].ToString();
                if (!dr.IsNull("COUNTRYCODE")) COUNTRYCODE = dr["COUNTRYCODE"].ToString();
                if (!dr.IsNull("BLNo")) BLNo = dr["BLNo"].ToString();
                if (!dr.IsNull("VNWRHSE")) VNWRHSE = dr["VNWRHSE"].ToString();
                if (!dr.IsNull("POLCODE")) POLCODE = dr["POLCODE"].ToString();
                if (!dr.IsNull("POLNAME")) POLNAME = dr["POLNAME"].ToString();
                if (!dr.IsNull("PODCODE")) PODCODE = dr["PODCODE"].ToString();
                if (!dr.IsNull("PODNAME")) PODNAME = dr["PODNAME"].ToString();
                if (!dr.IsNull("DLVRY")) DLVRY = dr["DLVRY"].ToString();
                if (!dr.IsNull("CURRENCY")) CURRENCY = dr["CURRENCY"].ToString();
                if (!dr.IsNull("REVENUE")) REVENUE = float.Parse(dr["REVENUE"].ToString());
                if (!dr.IsNull("GOODSCODE")) GOODSCODE = dr["GOODSCODE"].ToString();
                if (!dr.IsNull("GOODSNAME")) GOODSNAME = dr["GOODSNAME"].ToString();
                if (!dr.IsNull("VOLUME")) VOLUME = float.Parse(dr["VOLUME"].ToString());
                if (!dr.IsNull("UNIT")) UNIT = dr["UNIT"].ToString();
                if (!dr.IsNull("MADEIN")) MADEIN = dr["MADEIN"].ToString();
                if (!dr.IsNull("ROUTEBY")) ROUTEBY = dr["ROUTEBY"].ToString();
                if (!dr.IsNull("TYPE")) TYPE = dr["TYPE"].ToString();
                if (!dr.IsNull("CREATEDATE")) CREATEDATE = DateTime.Parse(dr["CREATEDATE"].ToString());
                if (!dr.IsNull("LASTUPDATE")) LASTUPDATE = DateTime.Parse(dr["LASTUPDATE"].ToString());
                if (!dr.IsNull("ProvinceID")) ProvinceID = int.Parse(dr["ProvinceID"].ToString());
            }
        }
        public static void GetList(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out int nTotalItem, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            nTotalItem = 0;
            string strSQL = "";
            ds = null;

            // build SQL statement
            strSQL = "SELECT tbl_CustomerLibrary.*,POLCountry.CountryName AS POLCountryName" +
                    ",PODCountry.CountryName AS PODCountryName,Provinces.Ten as ProvinceName" +
                    ",Goods.Name as GoodsNameSource " +
                    " FROM tbl_CustomerLibrary " +
                    " LEFT JOIN tbl_countries POLCountry  ON LEFT(tbl_CustomerLibrary.POLCODE,2)=POLCountry.CountryCode" +
                    " LEFT JOIN tbl_countries PODCountry  ON LEFT(tbl_CustomerLibrary.PODCODE,2)=PODCountry.CountryCode" +
                    " LEFT JOIN tbl_tinh Provinces  ON tbl_CustomerLibrary.ProvinceID=Provinces.ID" +
                    " LEFT JOIN tbl_goods Goods  ON tbl_CustomerLibrary.GoodsCode=Goods.Code" +
                    " WHERE 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, out nTotalItem, cn, trans);

            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }

        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_CustomerLibrary where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }

        //  DATE + Mã KH + POL + POD + TYPE + DLVRY + CNEE 
        public static long GetCustLibraryIDImportToLibrary(CustomerLibrary obj, out float revenue, out float volumn, MySqlConnection con, MySqlTransaction trans)
        {
            long lID = 0;
            float vRevenue = 0;
            float vVolumn = 0;
            string sqlCommand = "";
            // build SQL statement
            sqlCommand = "Select ID,REVENUE,VOLUME from tbl_CustomerLibrary" +
                " WHERE  1=1" +
                " AND NAME='" + DBModule.RefineString(obj.NAME) + "'" +
                " AND CODE='" + DBModule.RefineString(obj.CODE) + "'" +
                " AND PTNER='" + DBModule.RefineString(obj.PTNER) + "'" +
                " AND COUNTRYCODE='" + DBModule.RefineString(obj.COUNTRYCODE) + "'" +
                " AND BLNo='" + DBModule.RefineString(obj.BLNo) + "'" +
                " AND VNWRHSE='" + DBModule.RefineString(obj.VNWRHSE) + "'" +
                " AND POLCODE='" + DBModule.RefineString(obj.POLCODE) + "'" +
                " AND POLNAME='" + DBModule.RefineString(obj.POLNAME) + "'" +
                " AND PODCODE='" + DBModule.RefineString(obj.PODCODE) + "'" +
                " AND PODNAME='" + DBModule.RefineString(obj.PODNAME) + "'" +
                " AND DLVRY='" + DBModule.RefineString(obj.DLVRY) + "'" +
                " AND CURRENCY='" + DBModule.RefineString(obj.CURRENCY) + "'" +
                " AND GOODSCODE='" + DBModule.RefineString(obj.GOODSCODE) + "'" +
                " AND GOODSNAME='" + DBModule.RefineString(obj.GOODSNAME) + "'" +
                " AND UNIT='" + DBModule.RefineString(obj.UNIT) + "'" +
                " AND MADEIN='" + DBModule.RefineString(obj.MADEIN) + "'" +
                " AND ROUTEBY='" + DBModule.RefineString(obj.ROUTEBY) + "'" +
                " AND TYPE='" + DBModule.RefineString(obj.TYPE) + "'" +
                " AND Date(CREATEDATE)='" + obj.CREATEDATE.ToString("yyyy-MM-dd") + "'";
            // run SQL statement
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                lID = long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                try
                {
                    vRevenue = float.Parse(ds.Tables[0].Rows[0]["REVENUE"].ToString());
                }
                catch { }
                try
                {
                    vVolumn = float.Parse(ds.Tables[0].Rows[0]["VOLUME"].ToString());
                }
                catch { }
            }
            revenue = vRevenue;
            volumn = vVolumn;
            return lID;
        }
    }
}